package com.dashboard.giangday;

import java.sql.ResultSet;

import com.lb.jwitter.connectDatabase.ConnectTTGD;
import com.opensymphony.xwork2.Action;

public class ThongKeSoTietAction {
	private String jsonData;
	public String getJsonData() {
		return jsonData;
	}


	public void setJsonData(String jsonData) {
		this.jsonData = jsonData;
	}
	////////////////////////////////////////////////////////
	private String namHoc;

	public String getNamHoc() {
		return namHoc;
	}


	public void setNamHoc(String namHoc) {
		this.namHoc = namHoc;
	}
	/////////////////////////////////////////////////////////
	private String hocKy;

	public String getHocKy() {
		return hocKy;
	}


	public void setHocKy(String hocKy) {
		this.hocKy = hocKy;
	}
	/////////////////////////////////////////////////////////

	private String khoa;
	public String getKhoa() {
	return khoa;
	}
	
	
	public void setKhoa(String khoa) {
	this.khoa = khoa;
	}
	/////////////////////////////////////////////////////////
	private String boMon;
	
	public String getBoMon() {
	return boMon;
	}
	
	
	public void setBoMon(String boMon) {
	this.boMon = boMon;
	}
	////////////////////////////////////////////////////////
	public String execute() throws Exception
	{
		ConnectTTGD connectionOracle= new ConnectTTGD();
		if(connectionOracle.getConnection()!=null){
			namHoc="2013-2014";
			hocKy="1";
			khoa="MT";
			if(namHoc==null||namHoc.length()<1){
				return Action.ERROR;
			}
			if(hocKy==null||hocKy.length()<1){
				return Action.ERROR;
			}
			if(khoa==null||khoa.length()<1){
				return Action.ERROR;
			}
			int tongSoTiet=0;
			int somTre=0;
			int ndcb=0;
			int ndkb=0;
			int db=0;
			int chuadb=0;
			int dunggio=0;
			String query="";
			
			//Tinh so tiet som/tre, nghi day co bao, nghi day khong bao, day bu theo bo mon
			if(boMon!=null&&boMon.length()>0){
				query="select maGhichu, sum(thoiluong) as sotiet from sukiengiangday_view"
						+" where namhoc='"+namHoc+"' and hocky="+hocKy+" and maBoMon='"+boMon+"' and maGhichu<>'DTCB' and maGhichu<>'DTKB' "
						+" group by maGhichu";
			}
			//Tinh so tiet som/tre, nghi day co bao, nghi day khong bao, day bu theo khoa
			else{
				query="select maGhichu, sum(thoiluong) as sotiet from sukiengiangday_view"
						+" where namhoc='"+namHoc+"' and hocky="+hocKy+" and maKhoa='"+khoa+"' and maGhichu<>'DTCB' and maGhichu<>'DTKB' "
						+" group by maGhichu";
			}
			
			ResultSet data=connectionOracle.getData(query);
			if(data!=null){
				while(data.next()){
					String ghiChu=data.getString("maGhichu");
					int soTiet=data.getInt("sotiet");
					
					//So tiet day bu
					if(ghiChu.equals("DB")){
						db=soTiet;
					}
					//So tiet nghi day co bao
					else if(ghiChu.equals("NDCB")){
						ndcb=soTiet;
					}
					//So tiet nghi day khong bao
					else if(ghiChu.equals("NDKB")){
						ndkb=soTiet;			
					}
					//so phut vao tre
					else if(ghiChu.equals("VT")){
						somTre=somTre+soTiet;
					}
					//so phut ve som
					else if(ghiChu.equals("RS")){
						somTre=somTre+soTiet;
					}
					//So phut nghi giai lao qua gio
					else if(ghiChu.equals("NGLQG")){
						somTre=somTre+soTiet;
					}
				}
				//So tiet som/Tre
				somTre=somTre/45;
				//So tiet chua day bu
				chuadb=(ndcb+ndkb+somTre)-db;
				if(chuadb<0){
					chuadb=0;
				}
			}
			
			//Tinh tong so tiet len lop cua cac giang vien theo bo mon
			if(boMon!=null&&boMon.length()>0){
				query="select sum(sotietMonhoc) as tongSoTiet from"
						+" (select distinct  maMonHoc, sotietMonhoc,lophoc, maCb"
						+" from thoikhoabieu_view"
						+" where namhoc='2013-2014' and hocky=1 and maBoMon='"+boMon+"') as A";
			}
			
			//Tinh tong so tiet len lop cua cac giang vien theo khoa
			else{
				query="select sum(sotietMonhoc) as tongSoTiet from"
						+" (select distinct  maMonHoc, sotietMonhoc,lophoc, maCb"
						+" from thoikhoabieu_view"
						+" where namhoc='2013-2014' and hocky=1 and maKhoa='"+khoa+"') as A";
			}
			
			data=connectionOracle.getData(query);
			if(data!=null){
				if(data.next()){
					//Tinh tong so tiet len lop cua cac giang vien
					tongSoTiet=data.getInt("tongSoTiet");
				}
			}
			//Tinh so tiet dung gio cua cac giang vien
			dunggio=tongSoTiet-somTre;
			
			//{"tongSoTiet":1000,"dungGio":9000,"somTre":1000,"NDCB":200,"NDKB":100,"DB":250,"CDB":50}
			jsonData="{\"tongSoTiet\":"+tongSoTiet+",\"dungGio\":"+dunggio+",\"somTre\":"+somTre+",\"NDCB\":"+ndcb
					+",\"NDKB\":"+ndkb+",\"DB\":"+db+",\"CDB\":"+chuadb+"}";
		}
		connectionOracle.closeConnection();
		return Action.SUCCESS;

	}
}
